SQL SERVER – Using Decode in SQL Server

#SQL SERVER – Using Decode in SQL Server | 来源: 网络整理| 查看: 265

October 23, 2012 by Muhammad Imran

I was reading a function in Oracle namely “Decode”. It is very handy when it comes to search a variable and return the result based on search. It has been asked on multiple forums, what the EQUIVALENT of DECODE IN SQL SERVER is? Basically, we can achieve the same in SQL Server using Case and IIF statements.

Lets view the decode in Oracle.

Select DECODE(PoleDirection, 'North','N', 'South','S', 'East','E','WEST','W', 'Not Applicable') AS [Poles] FROM TablePoles

Lets view the Equivalent Code in SQL Server.

Declare @PoleDirection as varchar(10) Set @PoleDirection= 'North' SELECT CASE WHEN @PoleDirection = 'North' THEN 'N' WHEN @PoleDirection = 'South' THEN 'S' WHEN @PoleDirection = 'East' THEN 'E' WHEN @PoleDirection = 'WEST' THEN 'W' ELSE 'Not Applicable' END AS [Poles]

Decode function in SQL server

Given below is the DECODE function in SQL SERVER.

Create FUNCTION [dbo].[DECODE] ( @Expression nvarchar(max), @String nvarchar(Max)) RETURNS nvarchar(Max) AS Begin Declare @Delimiter as varchar(1) Declare @ReturnValue as nvarchar(max) Set @Delimiter=',' Declare @Xml AS XML Declare @Table TABLE( [ID] int Identity(1,1), Splitcolumn VARCHAR(MAX) ) SET @Xml = cast((''+replace(@String,@Delimiter,'')+'') AS XML) INSERT INTO @Table SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A) IF (Select Count (*) from @Table A Where @Expression=A.[SplitColumn])=1 Begin Select top 1 @ReturnValue=B.[Splitcolumn] from @Table A Left Join @Table B On A.[ID]

【本文地址】

公司简介

联系我们